package com.kcsj.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.kcsj.model.Evaluation;
import com.kcsj.model.User;
import com.kcsj.utils.DBUtil;

public class EvaluationDao {
	
	private UserDao userDao = new UserDao();
	
	public int addEvaluation(Connection con, Evaluation evaluation) {
		QueryRunner r = new QueryRunner();
		String sql = "insert into evaluation(content,goods_id,order_id,created_time,updated_time,creator_id) values(?,?,?,?,?,?)";
		try {
			Date data = new Date();
			return r.update(con, sql,evaluation.getContent(),evaluation.getGoodsId(),evaluation.getOrderId(),data,data,evaluation.getCreatorId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public int addReply(Connection con, Evaluation evaluation) {
		QueryRunner r = new QueryRunner();
		String sql = "insert into evaluation(content,goods_id,order_id,created_time,updated_time,creator_id,parent_id,to_user_id) values(?,?,?,?,?,?,?,?)";
		try {
			Date data = new Date();
			return r.update(con, sql,evaluation.getContent(),evaluation.getGoodsId(),evaluation.getOrderId(),data,data,evaluation.getCreatorId(),evaluation.getParentId(),evaluation.getToUserId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public int getLastInsertId(Connection con) throws SQLException {
		QueryRunner r = new QueryRunner();
		String sql = "select last_insert_id()";
		BigInteger bi = r.query(con, sql, new ScalarHandler<BigInteger>());
		return Integer.parseInt(bi.toString());
	}
	
	public Evaluation getMyEvaluation(int orderId,int goodsId,int userId) {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "select content,parent_id parentId,created_time createdTime,to_user_id toUserId from evaluation where order_id=? goods_id= ? and creator_id=? and parent_id=0";
		try {
			return r.query(sql, new BeanHandler<Evaluation>(Evaluation.class),orderId,goodsId,userId);
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return new Evaluation();
	}
	
	public Evaluation getWindowshoperOrderEvaluation(int orderId,int goodsId) {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "select id,content,parent_id parentId,created_time createdTime,to_user_id toUserId,goods_id goodsId,order_id orderId,creator_id creatorId from evaluation where order_id=? and goods_id=? and parent_id=0";
		try {
			Evaluation evaluation = r.query(sql, new BeanHandler<Evaluation>(Evaluation.class),orderId,goodsId);
			if(evaluation==null)
				return new Evaluation();
			User user = userDao.selectById(evaluation.getToUserId());
			String toUserName = user==null? "该用户已注销":user.getName();
			evaluation.setToUserName(toUserName);
			User user2 = userDao.selectById(evaluation.getCreatorId());
			String creatorUserName = user2==null? "该用户已注销":user2.getName();
			evaluation.setCreatorUserName(creatorUserName);
			QueryRunner r2 = new QueryRunner(DBUtil.getDataSource());
			String sql2 = "select id,content,parent_id parentId,created_time createdTime,to_user_id toUserId,goods_id goodsId,order_id orderId,creator_id creatorId from evaluation where order_id=? and goods_id=? and parent_id=?";
			try {
				List<Evaluation> evaluations = r2.query(sql2, new BeanListHandler<Evaluation>(Evaluation.class),orderId,goodsId,evaluation.getId());
				evaluations.forEach(evaluation2 -> {
					User subUser = null;
					try {
						subUser = userDao.selectById(evaluation2.getToUserId());
					} catch (SQLException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
					String subToUserName = subUser==null? "该用户已注销":subUser.getName();
					evaluation2.setToUserName(subToUserName);
					User subUser2 = null;
					try {
						subUser2 = userDao.selectById(evaluation2.getCreatorId());
					} catch (SQLException e) {
						e.printStackTrace();
					}
					String subCreatorUserName = subUser2==null? "该用户已注销":subUser2.getName();
					evaluation2.setCreatorUserName(subCreatorUserName);
				});
				evaluation.setSubList(evaluations);
				return evaluation;
			} catch (SQLException e) {
				e.printStackTrace();
			}	
			return evaluation;
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return new Evaluation();
	}
	
	public List<Evaluation> getGoodeEvaluation(int goodsId) {
		QueryRunner r = new QueryRunner(DBUtil.getDataSource());
		String sql = "select id,content,parent_id parentId,created_time createdTime,to_user_id toUserId,goods_id goodsId,order_id orderId,creator_id creatorId from evaluation where goods_id=? and parent_id=0";
		try {
			List<Evaluation> evaluations = r.query(sql, new BeanListHandler<Evaluation>(Evaluation.class),goodsId);
			if(evaluations==null)
				return new ArrayList<Evaluation>();
			evaluations.forEach(evaluation -> {
				User user = null;
				try {
					user = userDao.selectById(evaluation.getToUserId());
				} catch (SQLException e3) {
					e3.printStackTrace();
				}
				String toUserName = user==null? "该用户已注销":user.getName();
				evaluation.setToUserName(toUserName);
				User user2 = null;
				try {
					user2 = userDao.selectById(evaluation.getCreatorId());
				} catch (SQLException e2) {
					e2.printStackTrace();
				}
				String creatorUserName = user2==null? "该用户已注销":user2.getName();
				evaluation.setCreatorUserName(creatorUserName);
				QueryRunner r2 = new QueryRunner(DBUtil.getDataSource());
				String sql2 = "select id,content,parent_id parentId,created_time createdTime,to_user_id toUserId,goods_id goodsId,order_id orderId,creator_id creatorId from evaluation where goods_id=? and parent_id=?";
				try {
					List<Evaluation> subEvaluations = r2.query(sql2, new BeanListHandler<Evaluation>(Evaluation.class),goodsId,evaluation.getId());
					subEvaluations.forEach(evaluation2 -> {
						User subUser = null;
						try {
							subUser = userDao.selectById(evaluation2.getToUserId());
						} catch (SQLException e1) {
							// TODO Auto-generated catch block
							e1.printStackTrace();
						}
						String subToUserName = subUser==null? "该用户已注销":subUser.getName();
						evaluation2.setToUserName(subToUserName);
						User subUser2 = null;
						try {
							subUser2 = userDao.selectById(evaluation2.getCreatorId());
						} catch (SQLException e) {
							e.printStackTrace();
						}
						String subCreatorUserName = subUser2==null? "该用户已注销":subUser2.getName();
						evaluation2.setCreatorUserName(subCreatorUserName);
					});
					evaluation.setSubList(subEvaluations);
				} catch (SQLException e) {
					e.printStackTrace();
				}	
			});
			return evaluations;
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return new ArrayList<Evaluation>();
	}

}
